package com.jeecms.wicool.dao.orders.impl;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.jeecms.cms.entity.main.CmsUser;
import com.jeecms.common.hibernate3.Finder;
import com.jeecms.common.hibernate3.HibernateBaseDao;
import com.jeecms.common.page.Pagination;
import com.jeecms.common.util.DateUtils;
import com.jeecms.wicool.dao.orders.OrdersDao;
import com.jeecms.wicool.entity.orders.Orders;

@Repository
public class OrdersDaoImpl extends HibernateBaseDao<Orders, Integer> implements
		OrdersDao {

	@Override
	protected Class<Orders> getEntityClass() {
		return Orders.class;
	}

	@Override
	public Pagination getPage(String mobile, Integer disAddId,
			String orderCode, Integer shopId, String userName, Short status,
			Integer type, String startTime, String endTime, int pageNo,
			int pageSize, CmsUser user) {
		Finder f = Finder
				.create("select bean from Orders as bean inner join bean.user as user where bean.user.id is not null ");

		if (disAddId != null) {
			f.append(" and bean.distAddress.id = :disAddId ");
			f.setParam("disAddId", disAddId);
		}

		if (null != shopId) {
			f.append(" and bean.shop.id = :shopId");
			f.setParam("shopId", shopId);
		}
		if (!StringUtils.isBlank(userName)) {
			f.append(" and bean.consigneeName like :userName");
			f.setParam("userName", "%" + userName + "%");
		}

		if (!StringUtils.isBlank(mobile)) {
			f.append(" and bean.mobile like :mobile");
			f.setParam("mobile", Long.valueOf(mobile));
		}
		if (!StringUtils.isBlank(orderCode)) {
			f.append(" and bean.orderCode like :orderCode");
			f.setParam("orderCode", "%" + orderCode + "%");
		}
		if (null != status) {
			if (-1 == status.intValue()) {
				f.append(" and bean.status > 1");
			} else {
				f.append(" and bean.status = :status");
				f.setParam("status", status);
			}
		}
		if (type != null) {
			f.append(" and bean.type = :type");
			f.setParam("type", type);
		}
		if (StringUtils.isNotBlank(startTime)) {
			f.append(" and date_format(bean.orderDate,'%Y-%m-%d') >= :startTime");
			f.setParam("startTime", startTime);
		}
		if (StringUtils.isNotBlank(endTime)) {
			f.append(" and date_format(bean.orderDate,'%Y-%m-%d') <= :endTime");
			f.setParam("endTime", endTime);
		}
		f.append(" order by bean.id desc");
		return find(f, pageNo, pageSize);
	}

	@Override
	public Orders findById(Integer id) {
		Orders entity = get(id);
		return entity;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Orders> getAll(CmsUser user) {
		Finder f = Finder.create("select bean from Orders bean where 1=1 ");
		if (user.getShopId() != null) {
			f.append(" and bean.shop.id = :shopId");
			f.setParam("shopId", user.getShopId());
		}
		f.append(" order by bean.id desc");
		return find(f);
	}

	@SuppressWarnings("unchecked")
	public List<Orders> getAllGroupBuyOrders(Integer groupBuyId) {
		Finder f = Finder.create("select bean from Orders bean where 1=1 ");
		if (groupBuyId != null) {
			f.append(" and bean.typeId = :typeId and type=1 ");
			f.setParam("typeId", groupBuyId);
		}
		f.append(" order by bean.id desc");
		return find(f);
	}

	@Override
	public Pagination getPage(Integer type, Integer typeId, int pageNo,
			int pageSize) {
		Finder f = Finder
				.create("select bean from Orders as bean inner join bean.user as user where bean.user.id is not null  ");
		if (type != null) {
			f.append(" and bean.type = :type");
			f.setParam("type", type);
		}
		if (typeId != null) {
			f.append(" and bean.typeId = :typeId");
			f.setParam("typeId", typeId);
		}
		f.append(" order by bean.id desc");
		return find(f, pageNo, pageSize);
	}

	@Override
	public Pagination list(Short[] status, Integer disAddId, Date startTime,
			Date endTime, Integer typeId, Integer pageNo, Integer pageSize) {
		Finder f = Finder.create("select bean from Orders bean where 1=1 ");
		if (null != disAddId) {
			f.append(" and bean.distAddress.id=:disAddId ").setParam(
					"disAddId", disAddId);
		}

		if (null != startTime) {
			f.append(" and bean.orderDate>=:createTime").setParam("createTime",
					startTime);
		}

		if (null != endTime) {
			f.append(" and bean.orderDate<=:endTime").setParam("endTime",
					endTime);
		}

		if (null != status) {
			f.append(" and bean.status in (:status) ").setParamList("status",
					status);
		}

		if (null != typeId) {
			f.append(" and bean.typeId =:typeId ").setParam("typeId", typeId);
		}
		return find(f, pageNo, pageSize);
	}

	@Override
	public List<Orders> list(Short[] status, Integer disAddId, Date startTime,
			Date endTime, Integer typeId) {
		Finder f = Finder.create("select bean from Orders bean where 1=1 ");
		if (null != disAddId) {
			f.append(" and bean.distAddress.id=:disAddId ").setParam(
					"disAddId", disAddId);
		}

		if (null != startTime) {
			f.append(" and bean.orderDate>=:createTime").setParam("createTime",
					startTime);
		}

		if (null != endTime) {
			f.append(" and bean.orderDate<=:endTime").setParam("endTime",
					endTime);
		}

		if (null != status) {
			f.append(" and bean.status in (:status) ").setParamList("status",
					status);
		}

		if (null != typeId) {
			f.append(" and bean.typeId =:typeId ").setParam("typeId", typeId);
		}
		return find(f);
	}

	public List<Orders> list(String startTime, String endTime, Integer typeId) {
		Finder f = Finder
				.create("select bean from Orders bean where bean.status > 0 ");

		if (null != startTime) {
			f.append(
					" and date_format(bean.orderDate,'%Y-%m-%d') >=:createTime")
					.setParam("createTime", startTime);
		}

		if (null != endTime) {
			f.append(" and date_format(bean.orderDate,'%Y-%m-%d') <=:endTime")
					.setParam("endTime", endTime);
		}

		if (null != typeId) {
			f.append(" and bean.type =:type ").setParam("type", typeId);
		}
		return find(f);
	}

	public List<Orders> list(String startTime, String endTime) {
		Finder f = Finder
				.create("select bean from Orders bean where bean.status = 1 ");

		if (null != startTime) {
			try {
				f.append(" and bean.createTime >=:createTime").setParam(
						"createTime",
						new Timestamp(DateUtils.format3.parse(startTime)
								.getTime()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}

		if (null != endTime) {
			try {
				f.append(" and bean.createTime <=:endTime").setParam(
						"endTime",
						new Timestamp(DateUtils.format3.parse(endTime)
								.getTime()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		return find(f);
	}

	@Override
	public Pagination getPageForChildren(String school, String classes,
			String userName, String mobile, Integer productCateId,
			Integer week, String startTime, String endTime, int pageNo,
			int pageSize, CmsUser user) {
		
		String sql = "select date_format(o.orderDate,'%Y-%m-%d') as orderDate,"
				+ " pc.cate_name as cateName,u.school as school,u.classes as classes,"
				+ " o.consignee_name as userName,o.mobile as mobile,p.product_name as productName,"
				+ " opm.buy_count as buyCount,opm.product_price as uninPrice, opm.buy_count*opm.product_price as totalPrice,"
				+ " (select d.dic_name from dictionary d where o.payway=d.dic_code and d.type_code='pay_type') as payWay "
				+ " from orders o "
				+ " inner join user_info u on o.user_id=u.id "
				+ " inner join order_product_mapping opm ON o.id = opm.order_id "
				+ " inner join product p on p.id = opm.product_id "
				+ " inner join product_cate pc on p.product_cate_id=pc.id "
				+ " where 1=1 and o.type = 5";
		if(StringUtils.isNotBlank(school)){
			sql += " and u.school like '%"+school+"%'";
		}
		if(StringUtils.isNotBlank(classes)){
			sql += " and u.classes like '%"+classes+"%'";
		}
		
		if (!StringUtils.isBlank(userName)) {
			sql += " and o.consignee_name like '%"+userName+"%'";
		}

		if (!StringUtils.isBlank(mobile)) {
			sql += " and o.mobile = "+mobile+"";
		}
		
		if(productCateId != null){
			sql += " and pc.id = "+productCateId+"";
		}
		
		if(week != null){
			if (week == 1) {// 本周
				Calendar cal = Calendar.getInstance();
				cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
				cal.set(Calendar.HOUR_OF_DAY, 0);
				cal.set(Calendar.MINUTE, 0);
				cal.set(Calendar.SECOND, 0);
				Date monday = cal.getTime();

				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				if (StringUtils.isBlank(startTime)) {
					startTime = df.format(monday);
				}

				cal.set(Calendar.DAY_OF_WEEK, Calendar.FRIDAY);

				Date friday = cal.getTime();

				if (StringUtils.isBlank(endTime)) {
					endTime = df.format(friday);
				}
			} else if (week == 2) {// 下周
				Calendar cal = Calendar.getInstance();
				cal.add(Calendar.DATE, 7);
				cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
				cal.set(Calendar.HOUR_OF_DAY, 0);
				cal.set(Calendar.MINUTE, 0);
				cal.set(Calendar.SECOND, 0);
				Date monday = cal.getTime();

				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				if (StringUtils.isBlank(startTime)) {
					startTime = df.format(monday);
				}

				cal.set(Calendar.DAY_OF_WEEK, Calendar.FRIDAY);

				Date friday = cal.getTime();

				if (StringUtils.isBlank(endTime)) {
					endTime = df.format(friday);
				}
			}
		}
		
		
		if (StringUtils.isNotBlank(startTime)) {
			sql += " and  date_format(o.orderDate,'%Y-%m-%d') >= '"+startTime+"'";
		}
		if (StringUtils.isNotBlank(endTime)) {
			sql += " and  date_format(o.orderDate,'%Y-%m-%d') <= '"+endTime+"'";
		}
		sql += " order by o.id desc";
		Finder f = Finder.create(sql);
		return findBySql(f, pageNo, pageSize);
	}
	
	@Override
	public List<Map<String,Object>> getPageForExcel(String school, String classes,
			String userName, String mobile, Integer productCateId,
			Integer week, String startTime, String endTime, int pageNo,
			int pageSize, CmsUser user) {
		
		String sql = "select date_format(o.orderDate,'%Y-%m-%d') as orderTime, "
				+ " o.remark AS remark,"
				+ " DATE_FORMAT(o.orderDate, '%Y-%m-%d') AS orderDate, "
				+ " pc.cate_name AS cateName, "
				+ " u.school AS school, "
				+ " u.classes AS classes, "
				+ " o.consignee_name AS userName, "
				+ " p.product_name AS productName, "
				+ " SUM(opm.buy_count) AS buyCount, "
				+ " SUM(opm.buy_count * opm.product_price) AS totalPrice "
				+ " from orders o "
				+ " inner join user_info u on o.user_id=u.id "
				+ " inner join order_product_mapping opm ON o.id = opm.order_id "
				+ " inner join product p on p.id = opm.product_id "
				+ " inner join product_cate pc on p.product_cate_id=pc.id "
				+ " where 1=1 and o.type = 5 ";
		if(StringUtils.isNotBlank(school)){
			sql += " and u.school like '%"+school+"%'";
		}
		if(StringUtils.isNotBlank(classes)){
			sql += " and u.classes like '%"+classes+"%'";
		}
		
		if (!StringUtils.isBlank(userName)) {
			sql += " and o.consignee_name like '%"+userName+"%'";
		}

		if (!StringUtils.isBlank(mobile)) {
			sql += " and o.mobile = "+mobile+"";
		}
		
		if(productCateId != null){
			sql += " and pc.id = "+productCateId+"";
		}
		
		if(week != null){
			if (week == 1) {// 本周
				Calendar cal = Calendar.getInstance();
				cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
				cal.set(Calendar.HOUR_OF_DAY, 0);
				cal.set(Calendar.MINUTE, 0);
				cal.set(Calendar.SECOND, 0);
				Date monday = cal.getTime();

				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				if (StringUtils.isBlank(startTime)) {
					startTime = df.format(monday);
				}

				cal.set(Calendar.DAY_OF_WEEK, Calendar.FRIDAY);

				Date friday = cal.getTime();

				if (StringUtils.isBlank(endTime)) {
					endTime = df.format(friday);
				}
			} else if (week == 2) {// 下周
				Calendar cal = Calendar.getInstance();
				cal.add(Calendar.DATE, 7);
				cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
				cal.set(Calendar.HOUR_OF_DAY, 0);
				cal.set(Calendar.MINUTE, 0);
				cal.set(Calendar.SECOND, 0);
				Date monday = cal.getTime();

				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				if (StringUtils.isBlank(startTime)) {
					startTime = df.format(monday);
				}

				cal.set(Calendar.DAY_OF_WEEK, Calendar.FRIDAY);

				Date friday = cal.getTime();

				if (StringUtils.isBlank(endTime)) {
					endTime = df.format(friday);
				}
			}
		}
		
		
		if (StringUtils.isNotBlank(startTime)) {
			sql += " and  date_format(o.orderDate,'%Y-%m-%d') >= '"+startTime+"'";
		}
		if (StringUtils.isNotBlank(endTime)) {
			sql += " and  date_format(o.orderDate,'%Y-%m-%d') <= '"+endTime+"'";
		}
		sql += " GROUP BY u.school,u.classes,o.consignee_name,date_format(o.orderDate,'%Y-%m-%d'),pc.cate_name,p.product_name ";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> list = query.list();
		return list;
	}
}